﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.IO.Ports;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using MasterDatabase;

namespace Job_Assignment
{
    public partial class Form1 : SQL_APPL
    {
        void PlanByDate_Create_BT_Click(object sender, EventArgs e)
        {
            int count;
            string mess, sql_cmd;
            string strConnectionStringSourceDel = MasterDatabase_Connection_Str;
            string strConnectionStringSource = Kitting_Connection_Str;
            string strConnectionStringTarget = MasterDatabase_Connection_Str;
            
            bool b = KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Update_SQL_Data(KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Data_da, KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb);
            if (b)
            {
                FormatDataGridViewDisplay();
                DateSelect_Dialog_Form selectDate_Dialog = new DateSelect_Dialog_Form(DateTime.Now);
                if (selectDate_Dialog.ShowDialog() == DialogResult.OK)
                {
                    DateTime date = selectDate_Dialog.Select_Date;
                    //load data by date
                    sql_cmd = String.Format("SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_002_PlanForProductionByDate] WHERE [Date] = '{0}' order by LineId", date.ToString("yyyy-MM-dd"));
                    b = KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd);

                    if (b == false)
                    {
                        return;
                    }

                    count = KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb.Rows.Count;
                    if (count > 0)
                    {
                        mess = "Plan for date:" + date.ToString("dd MMM yyyy") + "was existing\n";
                        mess += "Do you want to delete and create the new one?";

                        if (MessageBox.Show(mess, "Warning", MessageBoxButtons.YesNo) == DialogResult.No)
                        {
                            return;
                        }
                        // DeletePlanfordate(date); 
                        //TODO: Add Funtion delete existing data in P_002_PlanForProductionByDate by date
                        
                        SqlConnection conn = null;
                        conn = new SqlConnection(strConnectionStringSourceDel);
                        try
                        {
                            conn.Open();
                            SqlCommand myCommand1 = new SqlCommand("TRUNCATE TABLE P_002_PlanForProductionByDate", conn);
                            myCommand1.ExecuteNonQuery();
                            MessageBox.Show("P_002_PlanForProductionByDate table cleared");
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Could not delete data from P_001_InputFromPlanner. " + ex.Message);
                            conn.Close();
                        }
                        //End of delete function

                    }

                    //TODO: Add Funtion to create data for P_002_PlanForProductionByDate
                    //Prepare connection string for databases ----------------------------------------------------------------
                    //Prepare temporary tables to store data ----------------------------------------------------------------
                    SqlConnection conn1, conn2 = null;
                    SqlDataAdapter daTABLENAME = null;
                    DataTable dtTABLENAME = null;
                    int intSourceTableCount = 0;

                    //Connect to source databse DLVNDB ----------------------------------------------------------------------
                    conn1 = new SqlConnection(strConnectionStringSource);
                    try
                    {
                        conn1.Open();
                        MessageBox.Show("Connected to DLVNDB.");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not connect to DLVNDB. " + ex.Message);
                    }

                    //Copy data to temporary table --------------------------------------------------------------------
                    try
                    {
                        daTABLENAME = new SqlDataAdapter("SELECT DATEADD(day,0, DATEDIFF(day,0,ActiveDateTime)) as Date, TopModel as PartNumber, SUM(POQty) as Qty, Sector as LineID, Sector as LineName " +
                                                         "FROM OpenPOPlanner " +
                                                         "WHERE DATEADD(day,0, DATEDIFF(day,0,ActiveDateTime))='" + date.Date.ToString() + "'" +
                                                         "GROUP BY DATEADD(day,0, DATEDIFF(day,0,ActiveDateTime)), TopModel, Sector", conn1);
                        dtTABLENAME = new DataTable();
                        daTABLENAME.Fill(dtTABLENAME);
                        intSourceTableCount = dtTABLENAME.Rows.Count;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not copy from OpenPOPlanner table. " + ex.Message);
                    }

                    //Copy data to PlanForProductionByDate Table -------------------------------------------------
                    conn2 = new SqlConnection(strConnectionStringTarget);
                    conn2.Open();
                    try
                    {
                        int i;
                        for (i = 0; i < intSourceTableCount; i++)
                        {
                            SqlCommand myCommand = new SqlCommand("INSERT INTO P_002_PlanForProductionByDate (Date, PartNumber, Qty, LineID, LineName) " +
                                                                "Values (" +
                                                                "'" + dtTABLENAME.Rows[i][0].ToString() + "'," +
                                                                "'" + dtTABLENAME.Rows[i][1].ToString() + "'," +
                                                                "'" + dtTABLENAME.Rows[i][2].ToString() + "'," +
                                                                "'" + dtTABLENAME.Rows[i][3].ToString() + "'," +
                                                                "'" + dtTABLENAME.Rows[i][4].ToString() + "')", conn2);

                            myCommand.ExecuteNonQuery();
                        }
                        MessageBox.Show(i.ToString() + " entri(es) copied.");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not copy to Production plan table. " + ex.Message);
                    }

                    dtTABLENAME.Dispose();
                    dtTABLENAME = null;
                    conn1.Close();
                    conn2.Close();

                    KeHoachSanXuatTheoNgayList_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd);
                }
                else
                {
                }
            }
        }
    }
}